<?php

class plus_mysql {

	private $db;
	private $trans_ing = false;
	
	public function __construct() {
		$dsn = 'mysql:host='.DB_HOST.';dbname='.DB_NAME.';port='.DB_PORT;
		for ($i=0;$i<3;$i++) { //如果连接Mysql失败则重试3次
			try{
				$this->db = new PDO($dsn, DB_USER, DB_PASS, array(
					PDO::ATTR_PERSISTENT => false,
					PDO::ATTR_CASE => PDO::CASE_NATURAL,
					PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
					PDO::ATTR_AUTOCOMMIT => true,
					PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
					PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
				));
				break;
			} catch (Exception $e) {
				if ($i >= 2){
				
				    core::error('连接数据库失败，请重试');
                }
			}
		}
	}
	/*
	 * 插入新记录行
	 * @param string $table 要操作的表名
	 * @param array $data 表结构及字段值
	 * return int 新记录行的自增ID
	 */
	public function insert($table, $data) {
		$fields = '';
		$values = '';
		$table = str_replace('@@',DB_PREFIX,$table);
		foreach ($data as $k => $v) {
			$fields .= '`'.$k.'`,';
			$values .= "'$v',";
		}
		$insertID = 0;
		try {
            $sql = 'INSERT INTO `' . $table . '` (' . substr($fields, 0, -1) . ') VALUES (' . substr($values, 0, -1) . ')';
            $insertID = $this->query($sql, 1);
        }catch (Exception $e){
		    $insertID = 0;
        }
        return $insertID;
	}
	public function replace($table,$data){
        $fields = '';
        $values = '';
        $table = str_replace('@@',DB_PREFIX,$table);
        foreach ($data as $k => $v) {
            $fields .= '`'.$k.'`,';
            $values .= "'$v',";
        }
        $sql = 'REPLACE INTO `'.$table.'` ('.substr($fields, 0, -1).') VALUES ('.substr($values, 0, -1).')';
        return $this->query($sql, 1);
    }
    /*
     * 删除表中指定的行
     * @param string $table 表名
     * @param array $where 限制表达式数组
     * return string $result 返回影响的行数
     */
    public function delete($table,$where=array()){
        $table = str_replace('@@',DB_PREFIX,$table);
        $wherevalues = '';
        $result = 0;
        foreach ($where as $k => $v) {
            $wherevalues .= "`".$k."`='".$v."',";
        }
        $wherevalues = substr($wherevalues, 0, -1);
        $wherevalues = str_replace(',',' AND ',$wherevalues);
        $result = $this->query("DELETE FROM `{$table}` WHERE {$wherevalues}",0);
        return $result;
    }
    /*
     * 删除表中指定字段表达式的行
     * @param string $table 表名
     * @param string $idfield 字段名
     * @param string $ids 要删除的字段值列表
     */
    public function delete_list($table,$idfield,$ids){
        $table = str_replace('@@',DB_PREFIX,$table);
        $sql = 'DELETE FROM `'.$table.'` WHERE `'.$idfield.'` in ('.$ids.')';
        return $this->db->query($sql,0);
    }
	/*
	 * 更新指定的记录行
	 * @param string @table 要更新的表名
	 * @param array @data 表结构及字段值
	 * @param string $where 更新记录行的限定表达式
	 * return int 影响的行数
	 */
	public function update($table, $data, $where=array()) {
		$table = str_replace('@@',DB_PREFIX,$table);
		$updatefields = '';
		$wherevalues = '';
		foreach ($data as $k => $v) {
			$updatefields .= "`".$k."`='".$v."',";
		}
        //$updatefields = substr($updatefields, 0, -1);
		foreach ($where as $k => $v) {
			$wherevalues .= "`".$k."`='".$v."',";
		}
		$wherevalues = substr($wherevalues, 0, -1);
		$wherevalues = str_replace(',',' AND ',$wherevalues);

		$sql = 'UPDATE `'.$table.'` set '.substr($updatefields, 0, -1).' WHERE '.$wherevalues.' LIMIT 1;';
		$sql = str_replace('AND LIMIT','LIMIT',$sql);
		return $this->query($sql, 0);
	}

	/*
	 * 取出表中指定单一个字段的值
	 * @param string $table 表名
	 * @param string $field 要取值的字段名
	 * @param array $where 要表名的限制表达式
	 * return string $result 返回字段的值
	 */
	public function getVal($table,$field,$where=array()){
        $table = str_replace('@@',DB_PREFIX,$table);
        $wherevalues = '';
        foreach ($where as $k => $v) {
            $wherevalues .= "`".$k."`='".$v."',";
        }
        $wherevalues = substr($wherevalues, 0, -1);
        $wherevalues = str_replace(',',' AND ',$wherevalues);
		$data = $this->query("SELECT `{$field}` FROM `{$table}` WHERE {$wherevalues} LIMIT 1",2);
		$result = $data ? $data[$field] : '';
		return $result;
	}
    /*
     * 取出表中一行的数据
     * @param string $table 表名
     * @param string $field 要取值的字段名
     * @param array $where 要表名的限制表达式
     * return array $result 返回当前行的数组
     */
    public function getRow($table,$field,$where=array()){
        $table = str_replace('@@',DB_PREFIX,$table);
        $wherevalues = '';
        foreach ($where as $k => $v) {
            $wherevalues .= "`".$k."`='".$v."',";
        }
        $wherevalues = substr($wherevalues, 0, -1);
        $wherevalues = str_replace(',',' AND ',$wherevalues);
        $data = $this->query("SELECT {$field} FROM `{$table}` WHERE {$wherevalues} LIMIT 1",2);
        $result = $data ? $data : array();
        return $result;
    }

    /*
     * 取指定表中指定字段的最大数值 + 1
     * @param string $field 要取值的字段（只能是数字型）
     * @param string $tablename 表名
     * return int 字段的最大值 + 1
     */
	public function maxval($field,$tablename){
		//$sql = str_replace('@@',DB_PREFIX,$sql);
		$tablename = str_replace('@@',DB_PREFIX,$tablename);
		$maxsql = "SELECT IFNULL(max(`".$field."`),0)+1 as vals FROM `".$tablename."` WHERE 1 LIMIT 1";
		$maxsort = $this->query($maxsql,2);
		$maxsort = $maxsort['vals'];
		return $maxsort;	
	}
	public function query($sql, $return) {
		$sql = str_replace('@@',DB_PREFIX,$sql);
		try {
			switch ($return) {
				case 0:
					$result = $this->db->exec($sql);
					//$result->closeCursor();
					break;
				case 1:
					$this->db->exec($sql);
					$result = $this->db->lastInsertId();
					break;
				case 2:
				case 3:
					$query = $this->db->query($sql);
					$action = $return === 2 ? 'fetch' : 'fetchAll';
					$result = call_user_func_array(array($query, $action), array(PDO::FETCH_ASSOC));
					$query->closeCursor();
				break;
				
				default: throw new Exception('_UNKNOW_RETURN_: '.$return);
			}
			return $result;
		} catch (Exception $e) {
			$data = array(
				'TYPE' => 'MYSQL',
				'SQL'  => $sql,
				'MSG' => $e->getMessage()
			);
			core::logger($data);
			if ($this->trans_ing) {
				throw new Exception('执行数据库操作失败，请重试');
			} else {
				core::error('执行数据库操作失败，请重试');
			}
		}
	}
	
	public function transaction($command) {
		try {
			switch ($command) {
				case 'begin':
					$this->db->beginTransaction();
					$this->trans_ing = true;
				break;
				
				case 'commit':
					$this->db->commit();
					$this->trans_ing = false;
				break;
				
				case 'rollBack':
					$this->db->rollBack();
				break;
				
				default:
			}
		} catch (Exception $e) {
			$data = array(
				'TYPE' => 'MYSQL',
				'SQL'  => '', //SQL语句
				'MSG' => $e->getMessage()
			);
			core::logger($data);
			if ($this->trans_ing) {
				throw new Exception('执行数据库操作失败，请重试');
			} else {
				core::error('执行数据库操作失败，请重试');
			}
		}
	}
	
	public function __destruct() {
		$this->db = null;
	}

}